# -*- coding: utf-8 -*-
from math import ceil
from service.services import tuple_to_set, strTuple, verifyTuple, currency_rate
from settings import db

class PaginationProduct():
    usersname = ''
    role = 0
    div = 0


    def __init__(self, div=100, user='all'):
        self.usersname, self.role = self.getUsernameRole(user)
        self.div = div

    def getCurrentPageProduct(self, page=1, like={}):
        countPage = ceil(self.countProduct(like) / self.div)
        return self.getProductsbyId(self.getProductsId(self.div * page, self.div, like))

    def getProductsId(self, leftLimit, rightLimit, like):
        if self.usersname == 'all':
            query = 'select entity_id from firstcatalog_product_entity '
        else:
            query = 'select product_id FROM firstadmin_product where admin_id=%s ' % self.usersname
        return db.sqlQuery(self.queryLike(query, like) + ' limit %s, %s' % (leftLimit, rightLimit))

    def queryLike(self, query, like):
        if like:
            if query.find("where") == -1:
                query += 'where '
            else:
                query += 'and '
            for key, value in like.items():
                query += '{0} like \'%{1}%\','.format(key, value)
            query = query[:len(query) - 1]
        return query

    def getUsernameRole(self, username):
        userId = db.sqlQuery("select user_id from firstadmin_user where username=\'" + username + "\'")
        role = db.sqlQuery("select parent_id from firstadmin_role where user_id=" + str(userId[0][0]))
        return role[0][0], str(userId[0][0])

    def countProduct(self, like):
        query = 'select count(*) from firstcatalog_product_entity '
        return db.sqlQuery(self.queryLike(query, like))[0][0]

    def getProductsbyId(self, productsId):
        productsTable = []
        for product in productsId:
        #            try:
            productAttribute = {}
            attributeType = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_varchar where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='common_type') and entity_id=" + str(
                    product[0]))
            attributeSetAndSku = db.sqlQuery("""SELECT attribute_set_name, sku FROM firstcatalog_product_entity join firsteav_attribute_set
                on firstcatalog_product_entity.attribute_set_id=firsteav_attribute_set.attribute_set_id
                where entity_id=""" + str(product[0]))
            productName = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_varchar where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='name' and  entity_type_id=4);")
            productPrice = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_decimal where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='price' and  entity_type_id=4);")
            productSpecialPrice = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_decimal where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='special_price' and  entity_type_id=4);")
            productSpecialPriceFrom = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_datetime where entity_id=" + str(
                    product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='special_from_date' and  entity_type_id=4);")
            productSpecialPriceTo = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_datetime where entity_id=" + str(
                    product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='special_to_date' and  entity_type_id=4);")
            productQty = db.sqlQuery(
                "SELECT qty FROM firstcataloginventory_stock_item where product_id=" + str(product[0]))
            if attributeType:
                t = verifyTuple(attributeType[0])
                if t:
                    type = db.sqlQuery(
                        "SELECT distinct value FROM firsteav_attribute_option_value where option_id in " + strTuple(
                            t))
                    productAttribute["type"] = type[0][0].split(',')
                else:
                    productAttribute["type"] = ""
            else:
                productAttribute["type"] = ""
            productAttribute["attribute_set"] = attributeSetAndSku[0][0]
            productAttribute["sku"] = attributeSetAndSku[0][1]
            productAttribute["name"] = productName[0][0]
            productAttribute["price"] = round(float(productPrice[0][0]), 2)
            productAttribute["price_usd"] = round(float(productPrice[0][0]) / currency_rate, 2)
            if productSpecialPrice[0][0]:
                productAttribute["special_price"] = round(float(productSpecialPrice[0][0]), 2)
                productAttribute["special_price_usd"] = round(float(productSpecialPrice[0][0]) / currency_rate, 2)
            if productSpecialPriceFrom[0][0]:
                productAttribute["special_from_date"] = str(productSpecialPriceFrom[0][0])
            else:
                productAttribute["special_from_date"] = ""
            if productSpecialPriceTo[0][0]:
                productAttribute["special_to_date"] = str(productSpecialPriceTo[0][0])
            else:
                productAttribute["special_to_date"] = ""
            productAttribute["qty"] = int(productQty[0][0])

            productsTable.append(productAttribute)
            #            except Exception:
        #                pass
        sets = db.sqlQuery("SELECT distinct attribute_set_name FROM firsteav_attribute_set")
        types = db.sqlQuery(
            "SELECT distinct value FROM firsteav_attribute_option_value where option_id in (SELECT option_id FROM firsteav_attribute_option where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='common_type'))")

        return productsTable, tuple_to_set(sets), tuple_to_set(types)